package com.zxx.sql;

import java.io.InputStream;
import java.util.ArrayList;

import jxl.Sheet;
import jxl.Workbook;
import android.content.Context;
import android.content.res.Resources;
import android.util.Log;

import com.soufang.ui.*;
import com.zxx.common.Common;

/**
 * @brief 数据库管理类
 * @author zxx
 * 
 */
public class sqlMan {
	
	private static sqlMan sDataMan; 
	private DataBaseHelper mHelper;
	private Context mAppContext;
	private houseData mhouseData;
		
	private int[] houseTypeIds = new int[] {
			  R.string.oneRoom, R.string.twoRoom, R.string.threeRoom
	  };
	 
	private enum TOTALPRICEKIND{
		ALL,
		BELOW100W,
		BETWEEN100W_200W,
		ABOVE_200W;
	}
	
	private enum PMPRICEKIND{
		BELOW1W,
		BETWEEN1W_2W,
		ABOVE_2W;
	}
	
	private sqlMan(Context appContext) {
        mAppContext = appContext;
        mHelper = new DataBaseHelper(mAppContext);
       
    }
	
	 /**
	  *  Description:单例模式
	  * @return sqlMan单例
	  */
	 public static sqlMan get(Context c) {
	        if (sDataMan == null) {
	            // we use the application context to avoid leaking activities
	        	sDataMan = new sqlMan(c.getApplicationContext());
	        }
	        return sDataMan;
	    }

	 /**
	  * Description:从assert文件夹的excel文件读取数据并插入数据库
	  * @return 0 成功，-1 失败
	  */
	 public long insertData()
	 {
		 int idCount = 0;
		 
		 try {
			 
			 Resources r = mAppContext.getResources();
			 InputStream in  = r.openRawResource(R.raw.house);
			 Workbook wb = Workbook.getWorkbook(in);
			 Sheet sheet = wb.getSheet(0);
			 int row = sheet.getRows();
			 int cols = sheet.getColumns();
			 for (int i = 1; i < row; ++i) {
				 String titleContents = sheet.getCell(0,i).getContents();
				 if( (null != titleContents) && (titleContents.length() >0 ) ) {
					 mhouseData = new houseData();
					 //getCell(col,row)
					 mhouseData.setMtitle(titleContents);
					 mhouseData.setMdepartMentName(sheet.getCell(1,i).getContents());
					 mhouseData.setMaddress(sheet.getCell(2,i).getContents());
					 Integer totalPrice = new Integer(sheet.getCell(3,i).getContents());
					 mhouseData.setMtotalprice(totalPrice);
					 Integer pmPrice = new Integer(sheet.getCell(4,i).getContents());
					 mhouseData.setMpmPrice(pmPrice);
					 mhouseData.setMorientation(sheet.getCell(5,i).getContents());
					 mhouseData.setMhouseType(sheet.getCell(6,i).getContents());
					 mhouseData.setMdecorate(sheet.getCell(7,i).getContents());
					 mhouseData.setMarea(sheet.getCell(8,i).getContents());
					 mhouseData.setMbuildType(sheet.getCell(9,i).getContents());
					 mhouseData.setMlouchen(sheet.getCell(10,i).getContents());
					 Integer year = new Integer(sheet.getCell(11,i).getContents());
					 mhouseData.setMbuildAge(year);
					 mhouseData.setMtel(sheet.getCell(12,i).getContents());
					 mhouseData.setMpicPath1(sheet.getCell(13,i).getContents());
					 mhouseData.setMpicPath2(sheet.getCell(14,i).getContents());
					 mhouseData.setMpicPath3(sheet.getCell(15,i).getContents());
					 mhouseData.setMpicPath4(sheet.getCell(16,i).getContents());
					 mhouseData.setMpicPath5(sheet.getCell(17,i).getContents());
					
					 if(!mHelper.selectRepeatData(mhouseData))
					 {
						 mHelper.insertData(mhouseData);
					 }else {
						 return -1;
					 }
					
				 }
				 
			 }
			  
		} 
		 catch (Exception e) {
			// TODO Auto-generated catch block
			 Log.e("sqlMan","we got big error here,Exception: "+Log.getStackTraceString(e));
			return -1;
		}
		 return 0;
	 }
	 /**
	  * Description:按默认模式选择数据库
	  * @return 数据库数据列表
	  */
	 public ArrayList<houseData> selectSQLDefault(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byIDL_H(limitindex,limitnum);
	 }
	 /**
	  * Description:按小区选择数据库
	  * @return 
	  */
	 public ArrayList<houseData> selectSQLByDepart(int position,int limitindex,int limitnum)
	 {
		 if(0 == position){
			 return mHelper.sort_byIDL_H(limitindex,limitnum);
		 }else{
			 String strText = mAppContext.getResources().getString(Common.GetDepartMenid(position));
			 return mHelper.select_byDepart(strText,limitindex,limitnum);
		 }
		 
	 }
	 /**
	  * Description:按总价选择数据库
	  * @return 
	  */
	 public ArrayList<houseData> selectSQLByTotalPrice(int position,int limitindex,int limitnum)
	 {
		 TOTALPRICEKIND kind = TOTALPRICEKIND.values()[position];
			 switch(kind)
			 {
			  case ALL:{
				 return mHelper.sort_byIDL_H(limitindex,limitnum);
			   }
			   case  BELOW100W:{
				   return mHelper.select_byTotalPricebelow100W(limitindex,limitnum); 
			   }
			   case BETWEEN100W_200W: {
				   return mHelper.select_byTotalPricebetween100_200(limitindex,limitnum);
			   }
			   case ABOVE_200W: {
				   return mHelper.select_byTotalPriceabove200W(limitindex,limitnum);
			   }
			   default:
				   return mHelper.sort_byIDL_H(limitindex,limitnum);
			 }
	 }
	 /**
	  * Description:按户型选择数据库
	  * @return 
	  */
	 public ArrayList<houseData> selectSQLByHouseType(int position,int limitindex,int limitnum)
	 {
		 String strText = mAppContext.getResources().getString(houseTypeIds[position]);
		 return mHelper.select_byHouseType(strText,limitindex,limitnum);
	 }
	 /**
	  * Description:按装修类型选择数据库
	  * @return 
	  */
	 public ArrayList<houseData> selectSQLByZX(int position,int limitindex,int limitnum)
	 {
		 int resID = Common.GetZXid(position);
		 String strText = mAppContext.getResources().getString(resID);
		 return mHelper.select_byzx(strText,limitindex,limitnum);
	 }
	 /**
	  * Description:按单价选择数据库
	  * @return 
	  */
	 public ArrayList<houseData> selectSQLByPmPrice(int position,int limitindex,int limitnum)
	 {
		 PMPRICEKIND kind = PMPRICEKIND.values()[position];
			 switch(kind)
			 {
			 case BELOW1W:{
				 return mHelper.select_byPmPricebelow1W(limitindex,limitnum);
			 }
			 case BETWEEN1W_2W: {
				  return mHelper.select_byPricebetween1w_2w(limitindex,limitnum);
			  }
			  case ABOVE_2W: {
				  return mHelper.select_byPmPriceabove2W(limitindex,limitnum);
			  }
			  default:
				  return mHelper.select_byPmPricebelow1W(limitindex,limitnum);
			 }
	 }
	 /**
	  * Description:按单价由高到低排序数据库
	  * @return 
	  */
	 public ArrayList<houseData> sort_byPriceH_L(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byPriceH_L(limitindex,limitnum);
	 }
	 /**
	  * FunctionName:sort_byPriceL_H
	  * Description: 按单价由低到高排序数据库
	  * @return 
	  */
	 public ArrayList<houseData> sort_byPriceL_H(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byPriceL_H(limitindex,limitnum);
	 }
	 /**
	  * FunctionName:sort_byYearH_L
	  * Description: 按年份由高到低排序数据库
	  * @return 
	  */
	 public ArrayList<houseData> sort_byYearH_L(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byYearH_L(limitindex,limitnum);
	 }
	 /**
	  * FunctionName:sort_byYearL_H
	  * Description: 按年份由低到高排序数据库
	  * @return 
	  */
	 public ArrayList<houseData> sort_byYearL_H(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byYearL_H(limitindex,limitnum);
	 }
	 /**
	  * FunctionName:sort_byAreaL_H
	  * Description: 按面积由低到高排序数据库
	  * @return 
	  */
	 public ArrayList<houseData> sort_byAreaL_H(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byAreaL_H(limitindex,limitnum);
	 }
	 /**
	  * FunctionName:sort_byAreaH_L
	  * Description: 按面积由高到低排序数据库
	  * @return 
	  */
	 public ArrayList<houseData> sort_byAreaH_L(int limitindex,int limitnum)
	 {
		 return mHelper.sort_byAreaH_L(limitindex,limitnum);
	 }
	 /**
	  * FunctionName:getSqlAllRow
	  * Description: 获取数据库默认排序的所有列数
	  * @return 列数
	  */
	 public int getSqlAllRow(){
		 return mHelper.getSortListRest();
	 }
	 /**
	  * FunctionName:getdepartmentAllRow
	  * Description: 获取数据库按小区排序的所有列数
	  * @return 列数
	  */
	public int getdepartmentAllRow(int position){
		if(position == 0){
			//区域的第一行是不限
			return mHelper.getSortListRest();
		}else{
			 String strText = mAppContext.getResources().getString(Common.GetDepartMenid(position));
			 String args[] = new String[]{strText};
			return mHelper.getDepartmentRest(args);
		}
	}
	 /**
	  * FunctionName:getTotalPriceAllrow
	  * Description: 获取数据库按单价选择后的所有列数
	  * @return 列数
	  */
	public int getTotalPriceAllrow(int position) {
		if(position == 0){
			//区域的第一行是不限
			return mHelper.getSortListRest();
		}else{
			if(position == 0){
				//区域的第一行是不限
				return mHelper.getSortListRest();
			}else{
				TOTALPRICEKIND kind = TOTALPRICEKIND.values()[position];
				 switch(kind)
				 {
				 case ALL:{
					 return mHelper.getSortListRest();
				 }
				 case BELOW100W:{
					 return mHelper.getTotalPricebelow100W();
				 }
				 case BETWEEN100W_200W: {
					  return mHelper.getTotalPrice100W_200W();
				  }
				  case ABOVE_200W: {
					  return mHelper.getTotalPriceabove200W();
				  }
				  default:
					  return mHelper.getSortListRest();
				 }
			}
		}
	}
	
	/**
	 * FunctionName:getHouseTypeRow
	 * Description: 获取按房子类型的剩余屋子总数
	 * @param position 列表上选择的position
	 * @return 
	 */
	public int getHouseTypeRow(int position) {
		String text = mAppContext.getResources().getString(houseTypeIds[position]);
		String args[] = new String[]{text+"%"};
		return mHelper.getHouseTypeRest(args);
		
	}
	/**
	 * FunctionName:getZXTypeRow
	 * Description: 获取按装修类型的剩余屋子总数
	 * @param position 列表上选择的position
	 * @return 
	 */
	public int getZXTypeRow(int position) {
		 int resID = Common.GetZXid(position);
		 String strText = mAppContext.getResources().getString(resID);
		String args[] = new String[]{strText};
		return mHelper.getZXTypeRest(args);
	}
	
	/**
	 * 
	 * Description: 获取按房子单价的剩余屋子总数
	 * @param position 列表上选择的position
	 * @return 
	 */
	public int getPmPriceAllrow(int position) {
		PMPRICEKIND kind = PMPRICEKIND.values()[position];
		 switch(kind)
		 {
		 case BELOW1W:{
			 return mHelper.getPmPriceBELOW1W();
		 }
		 case BETWEEN1W_2W: {
			  return mHelper.getPmPrice1W_2W();
		  }
		  case ABOVE_2W: {
			  return mHelper.getPmPriceabove2W();
		  }
		  default:
			  return mHelper.getSortListRest();
		 }
	}
}
